<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>xlsx.js导入导出excel | 漫漫江雪的个人博客</title>
    <meta name="generator" content="VuePress 1.8.2">
    
    <meta name="description" content="欢迎来到我的个人博客，这里是一些我个人的学习记录">
    <meta name="viewport" content="width=device-width,initial-scale=1,user-scalable=no">
    
    <link rel="preload" href="/blog/assets/css/0.styles.5830be0e.css" as="style"><link rel="preload" href="/blog/assets/js/app.48e74265.js" as="script"><link rel="preload" href="/blog/assets/js/3.a7fcd26b.js" as="script"><link rel="preload" href="/blog/assets/js/1.a8f59636.js" as="script"><link rel="preload" href="/blog/assets/js/20.9976b4b7.js" as="script"><link rel="prefetch" href="/blog/assets/js/10.0724209e.js"><link rel="prefetch" href="/blog/assets/js/11.6fefe01b.js"><link rel="prefetch" href="/blog/assets/js/12.86418e03.js"><link rel="prefetch" href="/blog/assets/js/13.97172a2d.js"><link rel="prefetch" href="/blog/assets/js/14.ab240604.js"><link rel="prefetch" href="/blog/assets/js/15.29a8e5d8.js"><link rel="prefetch" href="/blog/assets/js/16.992b7dee.js"><link rel="prefetch" href="/blog/assets/js/17.5d5a913b.js"><link rel="prefetch" href="/blog/assets/js/18.525625a3.js"><link rel="prefetch" href="/blog/assets/js/19.2168e3f4.js"><link rel="prefetch" href="/blog/assets/js/21.723f355f.js"><link rel="prefetch" href="/blog/assets/js/4.3a5eb401.js"><link rel="prefetch" href="/blog/assets/js/5.7271ce57.js"><link rel="prefetch" href="/blog/assets/js/6.d2b04da2.js"><link rel="prefetch" href="/blog/assets/js/7.a2b4e5a2.js"><link rel="prefetch" href="/blog/assets/js/8.a55c2fce.js"><link rel="prefetch" href="/blog/assets/js/9.ebc51634.js">
    <link rel="stylesheet" href="/blog/assets/css/0.styles.5830be0e.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container no-sidebar" data-v-1156296a><div data-v-1156296a><div id="loader-wrapper" class="loading-wrapper" data-v-d48f4d20 data-v-1156296a data-v-1156296a><div class="loader-main" data-v-d48f4d20><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div></div> <!----> <!----></div> <div class="password-shadow password-wrapper-out" style="display:none;" data-v-4e82dffc data-v-1156296a data-v-1156296a><h3 class="title" data-v-4e82dffc data-v-4e82dffc>漫漫江雪的个人博客</h3> <p class="description" data-v-4e82dffc data-v-4e82dffc>欢迎来到我的个人博客，这里是一些我个人的学习记录</p> <label id="box" class="inputBox" data-v-4e82dffc data-v-4e82dffc><input type="password" value="" data-v-4e82dffc> <span data-v-4e82dffc>Konck! Knock!</span> <button data-v-4e82dffc>OK</button></label> <div class="footer" data-v-4e82dffc data-v-4e82dffc><span data-v-4e82dffc><i class="iconfont reco-theme" data-v-4e82dffc></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-4e82dffc>vuePress-theme-reco</a></span> <span data-v-4e82dffc><i class="iconfont reco-copyright" data-v-4e82dffc></i> <a data-v-4e82dffc><span data-v-4e82dffc>漫漫江雪</span>
            
          <!---->
          2021
        </a></span></div></div> <div class="hide" data-v-1156296a><header class="navbar" data-v-1156296a><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/blog/" class="home-link router-link-active"><img src="/blog/avatar.jpg" alt="漫漫江雪的个人博客" class="logo"> <span class="site-name">漫漫江雪的个人博客</span></a> <div class="links"><div class="color-picker"><a class="color-button"><i class="iconfont reco-color"></i></a> <div class="color-picker-menu" style="display:none;"><div class="mode-options"><h4 class="title">Choose mode</h4> <ul class="color-mode-options"><li class="dark">dark</li><li class="auto active">auto</li><li class="light">light</li></ul></div></div></div> <div class="search-box"><i class="iconfont reco-search"></i> <input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/blog/" class="nav-link"><i class="iconfont reco-home"></i>
  首页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      分类
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/blog/categories/css/" class="nav-link"><i class="undefined"></i>
  css
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/vue3/" class="nav-link"><i class="undefined"></i>
  vue3
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/js/" class="nav-link"><i class="undefined"></i>
  js
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/koa/" class="nav-link"><i class="undefined"></i>
  koa
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/vuepress/" class="nav-link"><i class="undefined"></i>
  vuepress
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/git/" class="nav-link"><i class="undefined"></i>
  git
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/vue/" class="nav-link"><i class="undefined"></i>
  vue
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/node/" class="nav-link"><i class="undefined"></i>
  node
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/typescript/" class="nav-link"><i class="undefined"></i>
  typescript
</a></li></ul></div></div><div class="nav-item"><a href="/blog/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  标签
</a></div><div class="nav-item"><a href="/blog/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  时间线
</a></div><div class="nav-item"><a href="https://gitee.com/jxmlearner" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-mayun"></i>
  Gitee
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div> <!----></nav></div></header> <div class="sidebar-mask" data-v-1156296a></div> <aside class="sidebar" data-v-1156296a><div class="personal-info-wrapper" data-v-828910c6 data-v-1156296a><img src="/blog/avatar.jpg" alt="author-avatar" class="personal-img" data-v-828910c6> <h3 class="name" data-v-828910c6>
    漫漫江雪
  </h3> <div class="num" data-v-828910c6><div data-v-828910c6><h3 data-v-828910c6>11</h3> <h6 data-v-828910c6>文章</h6></div> <div data-v-828910c6><h3 data-v-828910c6>14</h3> <h6 data-v-828910c6>标签</h6></div></div> <ul class="social-links" data-v-828910c6></ul> <hr data-v-828910c6></div> <nav class="nav-links"><div class="nav-item"><a href="/blog/" class="nav-link"><i class="iconfont reco-home"></i>
  首页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      分类
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/blog/categories/css/" class="nav-link"><i class="undefined"></i>
  css
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/vue3/" class="nav-link"><i class="undefined"></i>
  vue3
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/js/" class="nav-link"><i class="undefined"></i>
  js
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/koa/" class="nav-link"><i class="undefined"></i>
  koa
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/vuepress/" class="nav-link"><i class="undefined"></i>
  vuepress
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/git/" class="nav-link"><i class="undefined"></i>
  git
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/vue/" class="nav-link"><i class="undefined"></i>
  vue
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/node/" class="nav-link"><i class="undefined"></i>
  node
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/typescript/" class="nav-link"><i class="undefined"></i>
  typescript
</a></li></ul></div></div><div class="nav-item"><a href="/blog/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  标签
</a></div><div class="nav-item"><a href="/blog/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  时间线
</a></div><div class="nav-item"><a href="https://gitee.com/jxmlearner" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-mayun"></i>
  Gitee
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div> <!----></nav> <!----> </aside> <div class="password-shadow password-wrapper-in" style="display:none;" data-v-4e82dffc data-v-1156296a><h3 class="title" data-v-4e82dffc data-v-4e82dffc>xlsx.js导入导出excel</h3> <!----> <label id="box" class="inputBox" data-v-4e82dffc data-v-4e82dffc><input type="password" value="" data-v-4e82dffc> <span data-v-4e82dffc>Konck! Knock!</span> <button data-v-4e82dffc>OK</button></label> <div class="footer" data-v-4e82dffc data-v-4e82dffc><span data-v-4e82dffc><i class="iconfont reco-theme" data-v-4e82dffc></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-4e82dffc>vuePress-theme-reco</a></span> <span data-v-4e82dffc><i class="iconfont reco-copyright" data-v-4e82dffc></i> <a data-v-4e82dffc><span data-v-4e82dffc>漫漫江雪</span>
            
          <!---->
          2021
        </a></span></div></div> <div data-v-1156296a><main class="page"><section><div class="page-title"><h1 class="title">xlsx.js导入导出excel</h1> <div data-v-1ff7123e><i class="iconfont reco-account" data-v-1ff7123e><span data-v-1ff7123e>漫漫江雪</span></i> <i class="iconfont reco-date" data-v-1ff7123e><span data-v-1ff7123e>2021-10-5</span></i> <!----> <i class="tags iconfont reco-tag" data-v-1ff7123e><span class="tag-item" data-v-1ff7123e>xlsx</span><span class="tag-item" data-v-1ff7123e>node</span></i></div></div> <div class="theme-reco-content content__default"><h2 id="导入"><a href="#导入" class="header-anchor">#</a> 导入</h2> <h3 id="导入前端"><a href="#导入前端" class="header-anchor">#</a> 导入前端</h3> <div class="language-html extra-class"><pre class="language-html"><code><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>el-upload</span> <span class="token attr-name">class</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>upload-demo<span class="token punctuation">&quot;</span></span> <span class="token attr-name">action</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>uploadExcel<span class="token punctuation">&quot;</span></span> <span class="token attr-name">:show-file-list</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>false<span class="token punctuation">&quot;</span></span> <span class="token attr-name">:on-success</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>uploadSuccess<span class="token punctuation">&quot;</span></span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>el-button</span> <span class="token attr-name">size</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>small<span class="token punctuation">&quot;</span></span> <span class="token attr-name">type</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>primary<span class="token punctuation">&quot;</span></span><span class="token punctuation">&gt;</span></span>上传Excel<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>el-button</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>el-upload</span><span class="token punctuation">&gt;</span></span>
</code></pre></div><div class="language-js extra-class"><pre class="language-js"><code><span class="token function">setup</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
  <span class="token keyword">const</span> tableData <span class="token operator">=</span> <span class="token function">ref</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token punctuation">)</span>

  <span class="token keyword">const</span> <span class="token function-variable function">uploadSuccess</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token parameter">response<span class="token punctuation">,</span> file<span class="token punctuation">,</span> fileList</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>response<span class="token punctuation">.</span>code <span class="token operator">===</span> <span class="token number">200</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
      console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>response<span class="token punctuation">.</span>data<span class="token punctuation">)</span>
      tableData<span class="token punctuation">.</span>value <span class="token operator">=</span> response<span class="token punctuation">.</span>data
      ElMessage<span class="token punctuation">.</span><span class="token function">success</span><span class="token punctuation">(</span>response<span class="token punctuation">.</span>msg<span class="token punctuation">)</span>
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span>

  <span class="token keyword">return</span> <span class="token punctuation">{</span>
    <span class="token operator">...</span>
    uploadSuccess
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre></div><h3 id="导入后端"><a href="#导入后端" class="header-anchor">#</a> 导入后端</h3> <div class="language-js extra-class"><pre class="language-js"><code><span class="token keyword">const</span> path <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">'path'</span><span class="token punctuation">)</span>
<span class="token keyword">const</span> fs <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">'fs'</span><span class="token punctuation">)</span>
<span class="token keyword">const</span> dayjs <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">'dayjs'</span><span class="token punctuation">)</span>
<span class="token keyword">const</span> xlsx <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">'xlsx'</span><span class="token punctuation">)</span>
<span class="token keyword">const</span> <span class="token punctuation">{</span> columnExcelMap<span class="token punctuation">,</span> excelColumnMap <span class="token punctuation">}</span> <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">'./column-map'</span><span class="token punctuation">)</span>
<span class="token comment">// const { Realtime } = require('../models')</span>
<span class="token keyword">const</span> <span class="token punctuation">{</span> History <span class="token punctuation">}</span> <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">'../models'</span><span class="token punctuation">)</span>

<span class="token keyword">function</span> <span class="token function">getFile</span><span class="token punctuation">(</span><span class="token parameter">reader<span class="token punctuation">,</span> upStream</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
  <span class="token keyword">return</span> <span class="token keyword">new</span> <span class="token class-name">Promise</span><span class="token punctuation">(</span><span class="token keyword">function</span><span class="token punctuation">(</span><span class="token parameter">result</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">let</span> stream <span class="token operator">=</span> reader<span class="token punctuation">.</span><span class="token function">pipe</span><span class="token punctuation">(</span>upStream<span class="token punctuation">)</span> <span class="token comment">// 可读流通过管道写入可写流</span>
    stream<span class="token punctuation">.</span><span class="token function">on</span><span class="token punctuation">(</span><span class="token string">'finish'</span><span class="token punctuation">,</span> <span class="token keyword">function</span><span class="token punctuation">(</span><span class="token parameter">err</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
      <span class="token function">result</span><span class="token punctuation">(</span>err<span class="token punctuation">)</span>
    <span class="token punctuation">}</span><span class="token punctuation">)</span>
  <span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token punctuation">}</span>

module<span class="token punctuation">.</span>exports <span class="token operator">=</span> <span class="token punctuation">{</span>
  <span class="token function-variable function">uploadExcel</span><span class="token operator">:</span> <span class="token keyword">async</span> <span class="token parameter">ctx</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
    <span class="token keyword">const</span> file <span class="token operator">=</span> ctx<span class="token punctuation">.</span>request<span class="token punctuation">.</span>files<span class="token punctuation">.</span>file <span class="token comment">// 获取上传文件</span>
    <span class="token keyword">const</span> reader <span class="token operator">=</span> fs<span class="token punctuation">.</span><span class="token function">createReadStream</span><span class="token punctuation">(</span>file<span class="token punctuation">.</span>path<span class="token punctuation">)</span> <span class="token comment">// 创建可读流</span>
    <span class="token keyword">const</span> ext <span class="token operator">=</span> file<span class="token punctuation">.</span>name<span class="token punctuation">.</span><span class="token function">split</span><span class="token punctuation">(</span><span class="token string">'.'</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">pop</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">// 获取上传文件扩展名</span>
    <span class="token keyword">const</span> fileName <span class="token operator">=</span> <span class="token function">dayjs</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">format</span><span class="token punctuation">(</span><span class="token string">'YYYYMMDDHHmmss'</span><span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token string">'.'</span> <span class="token operator">+</span> ext
    <span class="token keyword">const</span> savePath <span class="token operator">=</span> path<span class="token punctuation">.</span><span class="token function">resolve</span><span class="token punctuation">(</span>__dirname<span class="token punctuation">,</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string">../uploadExcels/</span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span>fileName<span class="token interpolation-punctuation punctuation">}</span></span><span class="token template-punctuation string">`</span></span><span class="token punctuation">)</span>
    <span class="token keyword">const</span> upStream <span class="token operator">=</span> fs<span class="token punctuation">.</span><span class="token function">createWriteStream</span><span class="token punctuation">(</span>savePath<span class="token punctuation">)</span> <span class="token comment">// 创建可写流</span>

    <span class="token keyword">const</span> nowStr <span class="token operator">=</span> <span class="token function">dayjs</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">format</span><span class="token punctuation">(</span><span class="token string">'YYYY-MM-DD HH:mm:ss'</span><span class="token punctuation">)</span>

    <span class="token keyword">try</span> <span class="token punctuation">{</span>
      <span class="token keyword">await</span> <span class="token function">getFile</span><span class="token punctuation">(</span>reader<span class="token punctuation">,</span> upStream<span class="token punctuation">)</span> <span class="token comment">// 等待数据存储完成</span>
      <span class="token keyword">const</span> workbook <span class="token operator">=</span> xlsx<span class="token punctuation">.</span><span class="token function">readFile</span><span class="token punctuation">(</span>savePath<span class="token punctuation">)</span>
      <span class="token keyword">let</span> json <span class="token operator">=</span> xlsx<span class="token punctuation">.</span>utils<span class="token punctuation">.</span><span class="token function">sheet_to_json</span><span class="token punctuation">(</span>workbook<span class="token punctuation">.</span>Sheets<span class="token punctuation">[</span>workbook<span class="token punctuation">.</span>SheetNames<span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
      <span class="token keyword">if</span> <span class="token punctuation">(</span>json <span class="token operator">&amp;&amp;</span> json<span class="token punctuation">.</span>length<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        json <span class="token operator">=</span> json<span class="token punctuation">.</span><span class="token function">map</span><span class="token punctuation">(</span><span class="token parameter">item</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
          <span class="token keyword">const</span> newItem <span class="token operator">=</span> <span class="token punctuation">{</span><span class="token punctuation">}</span>
          <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token keyword">const</span> <span class="token punctuation">[</span>itemKey<span class="token punctuation">,</span> itemValue<span class="token punctuation">]</span> <span class="token keyword">of</span> Object<span class="token punctuation">.</span><span class="token function">entries</span><span class="token punctuation">(</span>item<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token keyword">const</span> newKey <span class="token operator">=</span> excelColumnMap<span class="token punctuation">[</span>itemKey<span class="token punctuation">]</span>
            newItem<span class="token punctuation">[</span>newKey<span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token string">''</span> <span class="token operator">+</span> itemValue <span class="token comment">// 这里将一些整数都转换成字符串</span>
            <span class="token comment">// if (itemValue.length &gt; 10) {</span>
            <span class="token comment">//   console.log('xxxx', newKey, itemValue)</span>
            <span class="token comment">// }</span>
          <span class="token punctuation">}</span>
          <span class="token comment">// console.log(newItem)</span>
          newItem<span class="token punctuation">.</span>createAt <span class="token operator">=</span> nowStr
          <span class="token keyword">return</span> newItem
        <span class="token punctuation">}</span><span class="token punctuation">)</span>
      <span class="token punctuation">}</span>
      <span class="token keyword">await</span> <span class="token function">importToHistory</span><span class="token punctuation">(</span>json<span class="token punctuation">)</span>

      ctx<span class="token punctuation">.</span>body <span class="token operator">=</span> <span class="token punctuation">{</span>
        code<span class="token operator">:</span> <span class="token number">200</span><span class="token punctuation">,</span>
        data<span class="token operator">:</span> json<span class="token punctuation">,</span>
        msg<span class="token operator">:</span> <span class="token string">'上传成功'</span>
      <span class="token punctuation">}</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span>error<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span><span class="token string">'导入excel出错：'</span><span class="token punctuation">,</span> error<span class="token punctuation">)</span>
      ctx<span class="token punctuation">.</span>body <span class="token operator">=</span> <span class="token punctuation">{</span>
        code<span class="token operator">:</span> <span class="token number">500</span><span class="token punctuation">,</span>
        data<span class="token operator">:</span> <span class="token keyword">null</span><span class="token punctuation">,</span>
        msg<span class="token operator">:</span> <span class="token string">'数据导入不成功'</span>
      <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

<span class="token keyword">async</span> <span class="token keyword">function</span> <span class="token function">importToHistory</span><span class="token punctuation">(</span><span class="token parameter">data</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
  <span class="token keyword">try</span> <span class="token punctuation">{</span>
    <span class="token keyword">let</span> bulkData <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>Array<span class="token punctuation">.</span><span class="token function">isArray</span><span class="token punctuation">(</span>data<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
      <span class="token comment">// 如果传递过来的是数组</span>
      <span class="token keyword">for</span> <span class="token punctuation">(</span>d <span class="token keyword">of</span> data<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">const</span> <span class="token punctuation">[</span>history<span class="token punctuation">,</span> created<span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token keyword">await</span> History<span class="token punctuation">.</span><span class="token function">findOrCreate</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
          where<span class="token operator">:</span> <span class="token punctuation">{</span> code<span class="token operator">:</span> d<span class="token punctuation">.</span>code <span class="token punctuation">}</span><span class="token punctuation">,</span>
          defaults<span class="token operator">:</span> <span class="token punctuation">{</span> <span class="token operator">...</span>d <span class="token punctuation">}</span>
        <span class="token punctuation">}</span><span class="token punctuation">)</span>
        console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>created<span class="token punctuation">)</span>
      <span class="token punctuation">}</span>
    <span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token punctuation">{</span>
      <span class="token comment">// 如果是对象</span>
      <span class="token keyword">const</span> <span class="token punctuation">[</span>history<span class="token punctuation">,</span> created<span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token keyword">await</span> History<span class="token punctuation">.</span><span class="token function">findOrCreate</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
        where<span class="token operator">:</span> <span class="token punctuation">{</span> code<span class="token operator">:</span> data<span class="token punctuation">.</span>code <span class="token punctuation">}</span><span class="token punctuation">,</span>
        defaults<span class="token operator">:</span> <span class="token punctuation">{</span> <span class="token operator">...</span>data <span class="token punctuation">}</span>
      <span class="token punctuation">}</span><span class="token punctuation">)</span>
      console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>created<span class="token punctuation">)</span>
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span>error<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span><span class="token string">'将数据导入到表中时出错'</span><span class="token punctuation">,</span> error<span class="token punctuation">)</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre></div><h2 id="导出"><a href="#导出" class="header-anchor">#</a> 导出</h2> <h3 id="导出前端"><a href="#导出前端" class="header-anchor">#</a> 导出前端</h3> <div class="language-html extra-class"><pre class="language-html"><code><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>el-dropdown</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>el-button</span> <span class="token attr-name">type</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>primary<span class="token punctuation">&quot;</span></span><span class="token punctuation">&gt;</span></span> 导出Excel<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>i</span> <span class="token attr-name">class</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>el-icon-arrow-down el-icon--right<span class="token punctuation">&quot;</span></span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>i</span><span class="token punctuation">&gt;</span></span> <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>el-button</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>template</span> <span class="token attr-name">#dropdown</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>el-dropdown-menu</span><span class="token punctuation">&gt;</span></span>
      <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>el-dropdown-item</span> <span class="token attr-name">@click.native</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>exportSelected<span class="token punctuation">&quot;</span></span><span class="token punctuation">&gt;</span></span>导出选中项(仅点评数据)<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>el-dropdown-item</span><span class="token punctuation">&gt;</span></span>
      <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>el-dropdown-item</span> <span class="token attr-name">@click.native</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>exportCommentedAll<span class="token punctuation">&quot;</span></span><span class="token punctuation">&gt;</span></span>导出所有历史点评<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>el-dropdown-item</span><span class="token punctuation">&gt;</span></span>
      <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>el-dropdown-item</span> <span class="token attr-name">@click.native</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>exportHistory(<span class="token punctuation">'</span>query<span class="token punctuation">'</span>)<span class="token punctuation">&quot;</span></span><span class="token punctuation">&gt;</span></span>导出所有查询(无点评)<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>el-dropdown-item</span><span class="token punctuation">&gt;</span></span>
      <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>el-dropdown-item</span> <span class="token attr-name">@click.native</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">&quot;</span>exportHistory(<span class="token punctuation">'</span>allHistory<span class="token punctuation">'</span>)<span class="token punctuation">&quot;</span></span><span class="token punctuation">&gt;</span></span>导出所有历史数据(无点评)<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>el-dropdown-item</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>el-dropdown-menu</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>template</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>el-dropdown</span><span class="token punctuation">&gt;</span></span>
</code></pre></div><div class="language-js extra-class"><pre class="language-js"><code><span class="token function">setup</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
  <span class="token comment">// 导出选中项</span>
  <span class="token keyword">const</span> <span class="token function-variable function">exportSelected</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
    <span class="token keyword">const</span> $table <span class="token operator">=</span> xTable<span class="token punctuation">.</span>value
    <span class="token keyword">const</span> selectRecords <span class="token operator">=</span> $table<span class="token punctuation">.</span><span class="token function">getCheckboxRecords</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
    <span class="token keyword">const</span> commentRecords <span class="token operator">=</span> selectRecords<span class="token punctuation">.</span><span class="token function">filter</span><span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token parameter">x</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> x<span class="token punctuation">.</span>commentat<span class="token punctuation">)</span>
    <span class="token keyword">const</span> codes <span class="token operator">=</span> commentRecords<span class="token punctuation">.</span><span class="token function">map</span><span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token parameter">x</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> x<span class="token punctuation">.</span>code<span class="token punctuation">)</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token operator">!</span>codes<span class="token punctuation">.</span>length<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      ElMessage<span class="token punctuation">.</span><span class="token function">warning</span><span class="token punctuation">(</span><span class="token string">'请选择已点评数据导出'</span><span class="token punctuation">)</span>
      <span class="token keyword">return</span>
    <span class="token punctuation">}</span>
    console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>codes<span class="token punctuation">)</span>
    <span class="token function">commonExport</span><span class="token punctuation">(</span><span class="token punctuation">{</span> codes <span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token string">'selected'</span><span class="token punctuation">)</span>
  <span class="token punctuation">}</span>
  <span class="token comment">// 导出所有的历史点评</span>
  <span class="token keyword">const</span> <span class="token function-variable function">exportCommentedAll</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
    <span class="token function">commonExport</span><span class="token punctuation">(</span><span class="token keyword">null</span><span class="token punctuation">,</span> <span class="token string">'all'</span><span class="token punctuation">)</span>
  <span class="token punctuation">}</span>

  <span class="token keyword">const</span> <span class="token function-variable function">exportHistory</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token parameter">t</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>t <span class="token operator">===</span> <span class="token string">'query'</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
      <span class="token keyword">let</span> params <span class="token operator">=</span> <span class="token punctuation">{</span>
        code<span class="token operator">:</span> searchForm<span class="token punctuation">.</span>code<span class="token punctuation">,</span>
        createStart<span class="token operator">:</span> <span class="token punctuation">(</span>searchForm<span class="token punctuation">.</span>createAt <span class="token operator">&amp;&amp;</span> searchForm<span class="token punctuation">.</span>createAt<span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">)</span> <span class="token operator">||</span> <span class="token string">''</span><span class="token punctuation">,</span>
        createEnd<span class="token operator">:</span> <span class="token punctuation">(</span>searchForm<span class="token punctuation">.</span>createAt <span class="token operator">&amp;&amp;</span> searchForm<span class="token punctuation">.</span>createAt<span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">)</span> <span class="token operator">||</span> <span class="token string">''</span>
      <span class="token punctuation">}</span>
      <span class="token function">commonExport</span><span class="token punctuation">(</span>params<span class="token punctuation">,</span> t<span class="token punctuation">)</span>
    <span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token punctuation">{</span>
      <span class="token function">commonExport</span><span class="token punctuation">(</span><span class="token keyword">null</span><span class="token punctuation">,</span> t<span class="token punctuation">)</span>
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span>

  <span class="token keyword">const</span> <span class="token function-variable function">commonExport</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token parameter">params <span class="token operator">=</span> <span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">,</span> type</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
    <span class="token keyword">const</span> titleObj <span class="token operator">=</span> <span class="token punctuation">{</span>
      selected<span class="token operator">:</span> <span class="token string">'导出选中.xlsx'</span><span class="token punctuation">,</span>
      all<span class="token operator">:</span> <span class="token string">'所有历史点评.xlsx'</span><span class="token punctuation">,</span>
      query<span class="token operator">:</span> <span class="token string">'导出所有查询记录.xlsx'</span><span class="token punctuation">,</span>
      allHistory<span class="token operator">:</span> <span class="token string">'所有历史数据.xlsx'</span>
    <span class="token punctuation">}</span>
    <span class="token keyword">let</span> excelTitle <span class="token operator">=</span> titleObj<span class="token punctuation">[</span>type<span class="token punctuation">]</span> <span class="token operator">||</span> <span class="token string">'所有历史点评.xlsx'</span>
    <span class="token keyword">let</span> url <span class="token operator">=</span> <span class="token string">'/comment/export'</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>type <span class="token operator">===</span> <span class="token string">'query'</span> <span class="token operator">||</span> type <span class="token operator">===</span> <span class="token string">'allHistory'</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
      url <span class="token operator">=</span> <span class="token string">'/history/export'</span>
    <span class="token punctuation">}</span>

    axios<span class="token punctuation">.</span><span class="token function">post</span><span class="token punctuation">(</span>url<span class="token punctuation">,</span> params<span class="token punctuation">,</span> <span class="token punctuation">{</span> responseType<span class="token operator">:</span> <span class="token string">'blob'</span> <span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">then</span><span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token parameter">res</span><span class="token punctuation">)</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
      <span class="token keyword">const</span> stream <span class="token operator">=</span> res<span class="token punctuation">.</span>data <span class="token comment">// 后端用stream返回Excel文件</span>
      <span class="token keyword">const</span> blob <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Blob</span><span class="token punctuation">(</span><span class="token punctuation">[</span>stream<span class="token punctuation">]</span><span class="token punctuation">)</span>

      <span class="token comment">// 前端获取业务码，成功执行正常业务</span>
      <span class="token keyword">const</span> downloadElement <span class="token operator">=</span> document<span class="token punctuation">.</span><span class="token function">createElement</span><span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">)</span>
      <span class="token keyword">const</span> href <span class="token operator">=</span> window<span class="token punctuation">.</span><span class="token constant">URL</span><span class="token punctuation">.</span><span class="token function">createObjectURL</span><span class="token punctuation">(</span>blob<span class="token punctuation">)</span> <span class="token comment">// 创建下载的链接</span>
      downloadElement<span class="token punctuation">.</span>href <span class="token operator">=</span> href
      downloadElement<span class="token punctuation">.</span>download <span class="token operator">=</span> excelTitle <span class="token comment">// 下载后文件名</span>
      document<span class="token punctuation">.</span>body<span class="token punctuation">.</span><span class="token function">appendChild</span><span class="token punctuation">(</span>downloadElement<span class="token punctuation">)</span>
      downloadElement<span class="token punctuation">.</span><span class="token function">click</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">// 点击下载</span>
      document<span class="token punctuation">.</span>body<span class="token punctuation">.</span><span class="token function">removeChild</span><span class="token punctuation">(</span>downloadElement<span class="token punctuation">)</span> <span class="token comment">// 下载完成移除元素</span>
      window<span class="token punctuation">.</span><span class="token constant">URL</span><span class="token punctuation">.</span><span class="token function">revokeObjectURL</span><span class="token punctuation">(</span>href<span class="token punctuation">)</span> <span class="token comment">// 释放掉blob对象</span>
    <span class="token punctuation">}</span><span class="token punctuation">)</span>
  <span class="token punctuation">}</span>
  <span class="token keyword">return</span> <span class="token punctuation">{</span>
    <span class="token operator">...</span>
    exportSelected<span class="token punctuation">,</span>
    exportCommentedAll<span class="token punctuation">,</span>
    exportHistory
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre></div><h3 id="导出后端"><a href="#导出后端" class="header-anchor">#</a> 导出后端</h3> <div class="language-js extra-class"><pre class="language-js"><code><span class="token keyword">const</span> path <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">'path'</span><span class="token punctuation">)</span>
<span class="token keyword">const</span> fs <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">'fs'</span><span class="token punctuation">)</span>
<span class="token keyword">const</span> xlsx <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">'xlsx'</span><span class="token punctuation">)</span>
<span class="token keyword">const</span> <span class="token punctuation">{</span> columnExcelMap<span class="token punctuation">,</span> columnExcelSort<span class="token punctuation">,</span> excelColumnMap <span class="token punctuation">}</span> <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">'./column-map'</span><span class="token punctuation">)</span>
<span class="token keyword">const</span> <span class="token punctuation">{</span> sequelize <span class="token punctuation">}</span> <span class="token operator">=</span> <span class="token function">require</span><span class="token punctuation">(</span><span class="token string">'../models'</span><span class="token punctuation">)</span>

<span class="token keyword">let</span> commonSql <span class="token operator">=</span> <span class="token string">'select '</span>
columnExcelSort<span class="token punctuation">.</span><span class="token function">forEach</span><span class="token punctuation">(</span><span class="token parameter">x</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span>x <span class="token operator">===</span> <span class="token string">'days31InAim'</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    commonSql <span class="token operator">+=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string">A.&quot;</span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span>x<span class="token interpolation-punctuation punctuation">}</span></span><span class="token string">&quot; as 过</span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span>columnExcelMap<span class="token punctuation">[</span>x<span class="token punctuation">]</span><span class="token interpolation-punctuation punctuation">}</span></span><span class="token string">, \n</span><span class="token template-punctuation string">`</span></span> <span class="token comment">// sql的字段名不允许以数字开头，所以加个字符在前面</span>
  <span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token punctuation">{</span>
    commonSql <span class="token operator">+=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string">A.&quot;</span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span>x<span class="token interpolation-punctuation punctuation">}</span></span><span class="token string">&quot; as </span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span>columnExcelMap<span class="token punctuation">[</span>x<span class="token punctuation">]</span><span class="token interpolation-punctuation punctuation">}</span></span><span class="token string">, \n</span><span class="token template-punctuation string">`</span></span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
commonSql <span class="token operator">+=</span> <span class="token string">'A.&quot;createAt&quot; as 导入时间 from &quot;History&quot; as A where 1=1'</span>

module<span class="token punctuation">.</span>exports <span class="token operator">=</span> <span class="token punctuation">{</span>
  <span class="token function-variable function">page</span><span class="token operator">:</span> <span class="token keyword">async</span> <span class="token parameter">ctx</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
    <span class="token keyword">const</span> <span class="token punctuation">{</span> currentPage<span class="token punctuation">,</span> pageSize<span class="token punctuation">,</span> code<span class="token punctuation">,</span> createStart<span class="token punctuation">,</span> createEnd <span class="token punctuation">}</span> <span class="token operator">=</span> ctx<span class="token punctuation">.</span>request<span class="token punctuation">.</span>body
    <span class="token keyword">let</span> sql <span class="token operator">=</span> <span class="token string">'select A.*, B.&quot;createAt&quot; commentAt from &quot;History&quot; A left join &quot;Comment&quot; B on A.code=B.code where 1=1'</span>
    <span class="token keyword">let</span> countSql <span class="token operator">=</span> <span class="token string">'select count(1) as total from &quot;History&quot; A where 1=1'</span> <span class="token comment">// 计算总数</span>
    <span class="token keyword">let</span> replacements <span class="token operator">=</span> <span class="token punctuation">{</span><span class="token punctuation">}</span>
    <span class="token keyword">let</span> where <span class="token operator">=</span> <span class="token string">''</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>code<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      where <span class="token operator">+=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string"> and A.code like :code</span><span class="token template-punctuation string">`</span></span>
      replacements<span class="token punctuation">.</span>code <span class="token operator">=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string">%</span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span>code<span class="token interpolation-punctuation punctuation">}</span></span><span class="token string">%</span><span class="token template-punctuation string">`</span></span>
    <span class="token punctuation">}</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>createStart<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      where <span class="token operator">+=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string"> and A.&quot;createAt&quot;&gt;=:createStart</span><span class="token template-punctuation string">`</span></span>
      replacements<span class="token punctuation">.</span>createStart <span class="token operator">=</span> createStart
    <span class="token punctuation">}</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>createEnd<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      where <span class="token operator">+=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string"> and A.&quot;createAt&quot;&lt;=:createEnd</span><span class="token template-punctuation string">`</span></span>
      replacements<span class="token punctuation">.</span>createEnd <span class="token operator">=</span> createEnd
    <span class="token punctuation">}</span>
    sql <span class="token operator">+=</span> where
    countSql <span class="token operator">+=</span> where
    sql <span class="token operator">+=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string"> limit </span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span>pageSize<span class="token interpolation-punctuation punctuation">}</span></span><span class="token string"> offset </span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span><span class="token punctuation">(</span>currentPage <span class="token operator">-</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token operator">*</span> pageSize<span class="token interpolation-punctuation punctuation">}</span></span><span class="token string">;</span><span class="token template-punctuation string">`</span></span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
      <span class="token keyword">const</span> <span class="token punctuation">[</span>pageData<span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token keyword">await</span> sequelize<span class="token punctuation">.</span><span class="token function">query</span><span class="token punctuation">(</span>sql <span class="token operator">+</span> countSql<span class="token punctuation">,</span> <span class="token punctuation">{</span> replacements<span class="token punctuation">,</span> raw<span class="token operator">:</span> <span class="token boolean">true</span> <span class="token punctuation">}</span><span class="token punctuation">)</span>

      ctx<span class="token punctuation">.</span>body <span class="token operator">=</span> <span class="token punctuation">{</span>
        code<span class="token operator">:</span> <span class="token number">200</span><span class="token punctuation">,</span>
        data<span class="token operator">:</span> pageData<span class="token punctuation">,</span>
        msg<span class="token operator">:</span> <span class="token string">'获取分页数据成功'</span>
      <span class="token punctuation">}</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span>error<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span><span class="token string">'获取分页数据失败：'</span><span class="token punctuation">,</span> error<span class="token punctuation">)</span>
      ctx<span class="token punctuation">.</span>body <span class="token operator">=</span> <span class="token punctuation">{</span>
        code<span class="token operator">:</span> <span class="token number">500</span><span class="token punctuation">,</span>
        data<span class="token operator">:</span> <span class="token keyword">null</span><span class="token punctuation">,</span>
        msg<span class="token operator">:</span> <span class="token string">'获取分页数据失败'</span>
      <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span><span class="token punctuation">,</span>
  <span class="token function-variable function">exportExcel</span><span class="token operator">:</span> <span class="token keyword">async</span> <span class="token parameter">ctx</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
    console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span><span class="token string">'请求的数据'</span><span class="token punctuation">,</span> ctx<span class="token punctuation">.</span>request<span class="token punctuation">.</span>body<span class="token punctuation">)</span>
    <span class="token keyword">const</span> params <span class="token operator">=</span> ctx<span class="token punctuation">.</span>request<span class="token punctuation">.</span>body
    <span class="token keyword">let</span> execSql <span class="token operator">=</span> commonSql
    <span class="token keyword">let</span> excelTitle <span class="token operator">=</span> <span class="token string">'导出所有历史数据(无点评).xlsx'</span>
    <span class="token keyword">let</span> replacements <span class="token operator">=</span> <span class="token punctuation">{</span><span class="token punctuation">}</span>
    <span class="token keyword">let</span> where <span class="token operator">=</span> <span class="token string">''</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token string">'code'</span> <span class="token keyword">in</span> params<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      <span class="token comment">// 通过查询条件判断是否有code字段，如果有则说明是导出所有的查询历史数据</span>
      <span class="token keyword">if</span> <span class="token punctuation">(</span>params<span class="token punctuation">.</span>code<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        where <span class="token operator">+=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string"> and A.code like :code</span><span class="token template-punctuation string">`</span></span>
        replacements<span class="token punctuation">.</span>code <span class="token operator">=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string">%</span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span>params<span class="token punctuation">.</span>code<span class="token interpolation-punctuation punctuation">}</span></span><span class="token string">%</span><span class="token template-punctuation string">`</span></span>
      <span class="token punctuation">}</span>
      excelTitle <span class="token operator">=</span> <span class="token string">'导出所有查询(无点评).xlsx'</span>
      <span class="token keyword">const</span> <span class="token punctuation">{</span> createStart<span class="token punctuation">,</span> createEnd <span class="token punctuation">}</span> <span class="token operator">=</span> params
      <span class="token keyword">if</span> <span class="token punctuation">(</span>createStart<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span>createStart<span class="token punctuation">)</span> <span class="token punctuation">{</span>
          where <span class="token operator">+=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string"> and A.&quot;createAt&quot;&gt;=:createStart</span><span class="token template-punctuation string">`</span></span>
          replacements<span class="token punctuation">.</span>createStart <span class="token operator">=</span> createStart
        <span class="token punctuation">}</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span>createEnd<span class="token punctuation">)</span> <span class="token punctuation">{</span>
          where <span class="token operator">+=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string"> and A.&quot;createAt&quot;&lt;=:createEnd</span><span class="token template-punctuation string">`</span></span>
          replacements<span class="token punctuation">.</span>createEnd <span class="token operator">=</span> createEnd
        <span class="token punctuation">}</span>
      <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>
    <span class="token keyword">const</span> <span class="token punctuation">[</span>results<span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token keyword">await</span> sequelize<span class="token punctuation">.</span><span class="token function">query</span><span class="token punctuation">(</span>execSql <span class="token operator">+</span> where<span class="token punctuation">,</span> <span class="token punctuation">{</span> replacements<span class="token punctuation">,</span> raw<span class="token operator">:</span> <span class="token boolean">true</span> <span class="token punctuation">}</span><span class="token punctuation">)</span>
    <span class="token keyword">const</span> ws <span class="token operator">=</span> xlsx<span class="token punctuation">.</span>utils<span class="token punctuation">.</span><span class="token function">json_to_sheet</span><span class="token punctuation">(</span>results<span class="token punctuation">)</span>
    <span class="token keyword">const</span> wb <span class="token operator">=</span> xlsx<span class="token punctuation">.</span>utils<span class="token punctuation">.</span><span class="token function">book_new</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">// 创建一个空的workbook</span>
    xlsx<span class="token punctuation">.</span>utils<span class="token punctuation">.</span><span class="token function">book_append_sheet</span><span class="token punctuation">(</span>wb<span class="token punctuation">,</span> ws<span class="token punctuation">,</span> <span class="token string">'Sheet1'</span><span class="token punctuation">)</span>
    xlsx<span class="token punctuation">.</span><span class="token function">writeFileSync</span><span class="token punctuation">(</span>wb<span class="token punctuation">,</span> excelTitle<span class="token punctuation">)</span>

    <span class="token comment">//类型</span>
    ctx<span class="token punctuation">.</span>type <span class="token operator">=</span> <span class="token string">'.xlsx'</span>
    <span class="token comment">//请求返回，生成的xlsx文件</span>
    ctx<span class="token punctuation">.</span>body <span class="token operator">=</span> fs<span class="token punctuation">.</span><span class="token function">readFileSync</span><span class="token punctuation">(</span>excelTitle<span class="token punctuation">)</span>
    <span class="token comment">//请求返回后，删除生成的xlsx文件，不删除也行，下次请求回覆盖</span>
    fs<span class="token punctuation">.</span><span class="token function">unlink</span><span class="token punctuation">(</span>excelTitle<span class="token punctuation">,</span> <span class="token parameter">err</span> <span class="token operator">=&gt;</span> <span class="token punctuation">{</span>
      <span class="token keyword">if</span> <span class="token punctuation">(</span>err<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>err<span class="token punctuation">)</span>
        <span class="token keyword">throw</span> err
      <span class="token punctuation">}</span>
    <span class="token punctuation">}</span><span class="token punctuation">)</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre></div></div></section> <footer class="page-edit"><!----> <div class="last-updated"><span class="prefix">Last Updated: </span> <span class="time">2021-10-11 4:24:49 ├F10: PM┤</span></div></footer> <!----> <div class="comments-wrapper"><!----></div> <ul class="side-bar sub-sidebar-wrapper" style="width:12rem;" data-v-70334359><li class="level-2" data-v-70334359><a href="/blog/views/node/exportImport.html#导入" class="sidebar-link reco-side-导入" data-v-70334359>导入</a></li><li class="level-3" data-v-70334359><a href="/blog/views/node/exportImport.html#导入前端" class="sidebar-link reco-side-导入前端" data-v-70334359>导入前端</a></li><li class="level-3" data-v-70334359><a href="/blog/views/node/exportImport.html#导入后端" class="sidebar-link reco-side-导入后端" data-v-70334359>导入后端</a></li><li class="level-2" data-v-70334359><a href="/blog/views/node/exportImport.html#导出" class="sidebar-link reco-side-导出" data-v-70334359>导出</a></li><li class="level-3" data-v-70334359><a href="/blog/views/node/exportImport.html#导出前端" class="sidebar-link reco-side-导出前端" data-v-70334359>导出前端</a></li><li class="level-3" data-v-70334359><a href="/blog/views/node/exportImport.html#导出后端" class="sidebar-link reco-side-导出后端" data-v-70334359>导出后端</a></li></ul></main> <!----></div></div></div></div><div class="global-ui"><div class="back-to-ceiling" style="right:1rem;bottom:6rem;width:2.5rem;height:2.5rem;border-radius:.25rem;line-height:2.5rem;display:none;" data-v-c6073ba8 data-v-c6073ba8><svg t="1574745035067" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5404" class="icon" data-v-c6073ba8><path d="M526.60727968 10.90185116a27.675 27.675 0 0 0-29.21455937 0c-131.36607665 82.28402758-218.69155461 228.01873535-218.69155402 394.07834331a462.20625001 462.20625001 0 0 0 5.36959153 69.94390903c1.00431239 6.55289093-0.34802892 13.13561351-3.76865779 18.80351572-32.63518765 54.11355614-51.75690182 118.55860487-51.7569018 187.94566865a371.06718723 371.06718723 0 0 0 11.50484808 91.98906777c6.53300375 25.50556257 41.68394495 28.14064038 52.69160883 4.22606766 17.37162448-37.73630017 42.14135425-72.50938081 72.80769204-103.21549295 2.18761121 3.04276886 4.15646224 6.24463696 6.40373557 9.22774369a1871.4375 1871.4375 0 0 0 140.04691725 5.34970492 1866.36093723 1866.36093723 0 0 0 140.04691723-5.34970492c2.24727335-2.98310674 4.21612437-6.18497483 6.3937923-9.2178004 30.66633723 30.70611158 55.4360664 65.4791928 72.80769147 103.21549355 11.00766384 23.91457269 46.15860503 21.27949489 52.69160879-4.22606768a371.15156223 371.15156223 0 0 0 11.514792-91.99901164c0-69.36717486-19.13165746-133.82216804-51.75690182-187.92578088-3.42062944-5.66790279-4.76302748-12.26056868-3.76865837-18.80351632a462.20625001 462.20625001 0 0 0 5.36959269-69.943909c-0.00994388-166.08943902-87.32547796-311.81420293-218.6915546-394.09823051zM605.93803103 357.87693858a93.93749974 93.93749974 0 1 1-187.89594924 6.1e-7 93.93749974 93.93749974 0 0 1 187.89594924-6.1e-7z" p-id="5405" data-v-c6073ba8></path><path d="M429.50777625 765.63860547C429.50777625 803.39355007 466.44236686 1000.39046097 512.00932183 1000.39046097c45.56695499 0 82.4922232-197.00623328 82.5015456-234.7518555 0-37.75494459-36.9345906-68.35043303-82.4922232-68.34111062-45.57627738-0.00932239-82.52019037 30.59548842-82.51086798 68.34111062z" p-id="5406" data-v-c6073ba8></path></svg></div><!----></div></div>
    <script src="/blog/assets/js/app.48e74265.js" defer></script><script src="/blog/assets/js/3.a7fcd26b.js" defer></script><script src="/blog/assets/js/1.a8f59636.js" defer></script><script src="/blog/assets/js/20.9976b4b7.js" defer></script>
  </body>
</html>
